package com.css.fxfzypg.modules.resultsManagement.repository.impl;

import com.css.fxfzypg.base.attachment.entity.AttachmentInfoEntity;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.resultsManagement.entity.MapManagementEntity;
import com.css.fxfzypg.modules.resultsManagement.queryParams.MapManagementQueryParams;
import com.css.fxfzypg.modules.resultsManagement.repository.MapManagementRepository;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class MapManagementRepositoryImpl implements MapManagementRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void saveMap(MapManagementEntity mapManagementEntity) {
        String sql = "insert into map_management (map_id,task_id,attach_id,task_name,map_name,map_description," +
                "map_classify,map_label,task_district,uploader,upload_time,del_flag,create_user,create_time,title) values " +
                "(:mapId,:taskId,:attachId,:taskName,:mapName,:mapDescription,:mapClassify,:mapLabel,:taskDistrict," +
                ":uploader,:uploadTime,:delFlag,:createUser,:createTime,:title)";
        BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(mapManagementEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public MapManagementEntity getById(String id) {
        String sql = "select * from map_management where map_id='" + id + "' and del_flag ='" + YNEnum.N.toString() + "'";
        MapManagementEntity mapManagementEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(MapManagementEntity.class));
        return mapManagementEntity;
    }

    @Override
    public void deleteByIds(List<String> ids, Timestamp timestamp) {
        String sql = "update map_management set del_flag = '" + YNEnum.Y + "' , update_time = '" + timestamp + "' " + " where map_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);

    }

    @Override
    public MapManagementEntity update(MapManagementEntity mapManagementEntity) {
        String sql = "update map_management set task_id=:taskId,attach_id=:attachId,task_name=:taskName,map_name=:mapName,map_label=:mapLabel," +
                "map_description=:mapDescription,map_classify=:mapClassify,task_district=:taskDistrict,uploader=:uploader," +
                "upload_time=:uploadTime,del_flag=:delFlag,update_user=:updateUser,update_time=:updateTime,title=:title where map_id=:mapId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(mapManagementEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return mapManagementEntity;
    }

    @Override
    public Map<String, Object> queryMaps(MapManagementQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from map_management ");
        StringBuilder whereSql = new StringBuilder("where del_flag = '" + YNEnum.N.toString() + "' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getMapClassify())) {
            whereSql.append(" and map_classify like '%" + queryParams.getMapClassify() + "%' ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getUploader())) {
            whereSql.append(" and uploader like '%" + queryParams.getUploader() + "%' ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and task_name like '%" + queryParams.getTaskName() + "%' ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskDistrict())) {
            whereSql.append(" and task_district like '%" + queryParams.getTaskDistrict() + "%' ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and upload_time between '" + queryParams.getStartTime() + "' and '" + queryParams.getEndTime() + "'");
        }
        StringBuilder orderSql = new StringBuilder(" order by upload_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql1 = sql.append(whereSql);
        Integer total = jdbcTemplate.query(sql1.toString(), new BeanPropertyRowMapper<>(MapManagementEntity.class)).size();
        String sql2 = sql1.append(orderSql).append(limitSql).toString();
        List<MapManagementEntity> maps = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(MapManagementEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", maps);
        return map;
    }

    @Override
    public MapManagementEntity getByLabel(String taskId, String mapLabel) {
        try {
            String sql = "select * from map_management where task_id='" + taskId + "' and del_flag ='" + YNEnum.N.toString() + "' and map_label ='" + mapLabel + "'";
            MapManagementEntity mapManagementEntitity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(MapManagementEntity.class));
            return mapManagementEntitity;
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<MapManagementEntity> getByLabel(String taskId) {
        try {
            String sql = "select * from map_management where task_id='" + taskId + "' and del_flag ='" + YNEnum.N.toString() + "'";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(MapManagementEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<String> getMapClassifyByTaskId(String taskId) {
        return jdbcTemplate.queryForList(String.format("SELECT map_classify FROM map_management where task_id = '%s' GROUP BY map_classify", taskId), String.class);
    }


}
